/**
 * Created by wry on 2015/5/14.
 */
var toolUtil = require('../../common/utils/toolUtil');
var mysqlUtil = require('../../common/utils/mysqlUtil');
var users = mongoose.model('user');
var async = require('async');
var moment = require('moment');

exports.addVoucherTypeService = function (input, callback) {
    mysqlUtil.query('select * from voucher_type where type=? and voucher_val=?', [input.type, input.voucher_val], function (err, result) {
        if (err || result == null || result.length != 0) {
            var msg = {
                message: '同类型价值重复'
            };
            callback(msg, null);
        } else {
            input.create_time = new Date().getTime();
            mysqlUtil.query('insert into voucher_type set?', input, callback)
        }
    });
};

exports.getVoucherTypeService = function (callback) {
    var sql = 'select id,voucher_name,voucher_val,type from voucher_type';
    mysqlUtil.query(sql, callback)
};

exports.addRetailerService = function (input, callback) {

    if (input.id == null) {
        mysqlUtil.query('select * from retailer where code=?', [input.code], function (err, result) {
            if (err || result == null || result.length != 0) {
                var msg = {
                    message: '商户编号不能重复'
                };
                callback(msg, null);
            } else {
                input.create_time = new Date().getTime();
                mysqlUtil.query('insert into retailer set?', input, callback);
            }
        });
    } else {
        var id = input.id;
        delete input.id;
        delete input.communities;
        delete input.community;

        if (input.starting_time.indexOf('000') != -1) {
            input.starting_time = Number(input.starting_time)
        } else {
            input.starting_time = moment(input.starting_time).toDate().getTime();
        }

        mysqlUtil.query('select * from retailer where code=?', [input.code], function (err, result) {
            if (result.length > 0 && result[0].id != id) {
                var msg = {
                    message: '商户编号不能重复'
                };
                callback(msg, null);
            } else {
                input.update_time = new Date().getTime();
                mysqlUtil.query('update retailer set ? where id = ?', [input, id], callback)
            }
        });
    }
};

exports.addMainRetailerService = function (input, callback) {
    if (input.id == null) {
        input.create_time = new Date().getTime();
        mysqlUtil.query('insert into retailer set?', input, callback);
    } else {
        logger.info(input);
        var id = input.id;
        var data = {
            retailer_name: input.retailer_name,
            contact_name: input.contact_name,
            phone: input.phone,
            starting_time: input.starting_time
        };
        mysqlUtil.query('update retailer set ? where id=?', [data, id], callback);
    }
};

exports.getRetailerListService = function (parentId, callback) {
    var sql = 'select * from retailer where parent_id=' + '"' + parentId + '"';

    mysqlUtil.query(sql, function (err, results) {
        async.each(results, function (result, callbackInner) {
            mysqlUtil.query('select community_id from retailer_community_bind where retailer_id=?', [result.id], function (err, communities) {
                result.communities = '';
                result.community = [];
                communities.forEach(function (communityId) {
                    result.communities += communityId.community_id + ',';
                    result.community.push(communityId.community_id);
                });
                callbackInner()
            })
        }, function (err) {
            results.forEach(function (temp) {
                temp.starting_time = moment(new Date(temp.starting_time)).format('YYYY-MM-DD');
            });
            callback(err, results)
        })
    });
};

exports.searchRetailerByNameService = function (parentId, name, address, communityId, callback) {
    name = name ? name : '';
    address = address ? address : '';
    if (parentId == -1) {
        mysqlUtil.query('SELECT * FROM retailer r LEFT JOIN retailer_community_bind b ON r.id=b.retailer_id and b.community_id=? having parent_id <> 0 and retailer_name like? and address like?', [communityId, '%' + name + '%', '%' + address + '%'], callback)
    } else {
        mysqlUtil.query('select * from retailer where parent_id=? and retailer_name like? and address like?', [parentId, '%' + name + '%', '%' + address + '%'], callback);
    }
};

exports.bindRetailerCommunityService = function (retailerId, communities, callback) {
    async.series([
        function (callbackInner) {
            var sql = 'delete from retailer_community_bind where retailer_id = ?';
            mysqlUtil.query(sql, [retailerId], callbackInner)
        },
        function (callbackInner) {
            var data = [];
            if (communities != null && communities.length > 0) {
                communities.forEach(function (communityId) {
                    var temp = [retailerId, communityId];
                    data.push(temp);
                });
                var sql = 'insert into retailer_community_bind (retailer_id,community_id) values ?';
                mysqlUtil.query(sql, [data], callbackInner);
            } else {
                callbackInner();
            }
        }
    ], callback)
};

exports.getAllRetailers = function (callback) {
    var sql = 'select * from retailer';
    mysqlUtil.query(sql, callback);
}

exports.getAgreementVoucherService = function (callback) {
    mysqlUtil.query('select * from voucher_type where type=1', callback)
};

exports.getRetailerAgreementService = function (retailerId, callback) {
    var sql = 'select * from voucher_type v,retailer_agreement a where a.retailer_id=? and a.voucher_type_id=v.id';
    mysqlUtil.query(sql, [retailerId], callback)
};

exports.addRetailerAgreementService = function (input, callback) {

    if (input.id == null) {
        mysqlUtil.query('select * from retailer_agreement where retailer_id=? and voucher_type_id=?', [input.retailer_id, input.voucher_type_id], function (err, result) {
            if (err || result.length > 0) {
                logger.error(err ? err : '此协议已添加');
                callback({message: '此协议已添加'})
            } else {
                input.create_time = new Date().getTime();
                mysqlUtil.query('insert into retailer_agreement set?', input, callback)
            }
        })
    } else {
        var id = input.id;
        delete input.id;

        input.update_time = new Date().getTime();
        mysqlUtil.query('update retailer_agreement set ? where id = ?', [input, id], callback)
    }
};

exports.deleteAgreementService = function (id, callback) {
    mysqlUtil.query('delete from retailer_agreement where id=?', [id], callback)
};


exports.getBranchRetailerService = function (communityId, page, callback) {
    page = Number(page);
    var index = page == 1 ? 0 : ((page - 1) * listPageSize) - 1;
    async.series([
        function (callbackInner) {
            mysqlUtil.query('select count(*) as count from retailer WHERE parent_id <> 0', callbackInner)
        },
        function (callbackInner) {
            mysqlUtil.query('SELECT * FROM retailer r LEFT JOIN retailer_community_bind b ON r.id=b.retailer_id and b.community_id=? where parent_id <> 0 limit ?,?', [communityId, index, listPageSize], callbackInner)
        }
    ], callback);
};

exports.singleBindRetailerService = function (input, callback) {
    if (!input.status) {
        var data = {
            retailer_id: input.id,
            community_id: input.communityId
        };
        mysqlUtil.query('insert into retailer_community_bind set?', data, callback)
    } else {
        mysqlUtil.query('delete from retailer_community_bind where retailer_id=? and community_id=?', [input.id, input.communityId], callback);
    }
};

exports.getRetailerExchangeList = function (input, callback) {
    var currentPage = 1;
    if (input.currentPage != '' && input.currentPage != null) {
        currentPage = input.currentPage;
    }
    var pageSize = 10;
    var offset = (currentPage - 1) * pageSize;

    var getSql = function (type) {
        var tmpSql = "select distinct " + type +
            " from voucher_retailer_profile as vrp " +
            " left join retailer as r on vrp.retailer_id = r.id " +
            " left join voucher_type as vt on vrp.voucher_type_id = vt.id " +
            " left join retailer_community_bind as rcb on vrp.retailer_id = rcb.retailer_id " +
            " left join voucher_user_bind as vub on vrp.voucher_user_bind_id = vub.id " +
            " where 1=1";

        if (input.name != '' && input.name != null) {
            tmpSql += ' and r.retailer_name like "%' + input.name + '%"';
        }
        if (input.status != '' && input.status != null) {
            tmpSql += ' and vrp.status = ' + input.status;
        }

        if (input.fromDate != '' && input.fromDate != null) {
            tmpSql += ' and vrp.create_time > ' + input.fromDate;
        }

        if (input.fromDate != '' && input.fromDate != null && input.toDate != '' && input.toDate != null) {
            tmpSql += ' and vrp.create_time between ' + input.fromDate + ' and ' + input.toDate;
        }

        if (input.exFromDate != '' && input.exFromDate != null) {
            tmpSql += ' and vrp.close_time > ' + input.exFromDate;
        }

        if (input.exFromDate != '' && input.exFromDate != null && input.exToDate != '' && input.exToDate != null) {
            tmpSql += ' and vrp.close_time between ' + input.exFromDate + ' and ' + input.exToDate;
        }
        if (input.code != '' && input.code != null) {
            tmpSql += ' and r.code = ' + input.code;
        }
        if (input.cardNo != '' && input.cardNo != null) {
            tmpSql += ' and vub.card_no = "' + input.cardNo + '" ';
        }
        tmpSql += ' order by vrp.create_time desc';

        return tmpSql;
    }

    async.series([
        function (callbackInner) {

            var sql = getSql("vrp.*,r.retailer_name,r.code,vt.voucher_val,vub.community_name,vub.card_no,vub.create_time as genTime ,vub.spending_time ") + ' limit ' + offset + ' ,' + pageSize;

            mysqlUtil.query(sql, function (err, result) {
                async.each(result, function (temp, callbackInner) {
                    users.findOne({_id: temp.user_id}, function (err, user) {
                        temp.info = user;
                        callbackInner();
                    });
                }, function (err) {
                    callbackInner(err, result);
                });
            });
        },
        function (callbackInner) {
            var sql = getSql("count(*) as count");

            mysqlUtil.query(sql, function (err, result) {
                var count = result[0].count;
                var totalPage = Math.ceil(count / pageSize);
                callbackInner(err, totalPage);
            });
        }
    ], callback)
};

exports.searchVoucherService = function (input, page, callback) {

    var offset = (Number(page) - 1) * listPageSize;

    var getSql = function (field) {
        var sql = 'SELECT ' + field +
            ' FROM voucher_user_bind b ' +
            ' LEFT JOIN voucher_type t ' +
            'ON b.voucher_type_id=t.id ' +
            'LEFT JOIN voucher_retailer_profile p ' +
            'on b.id = p.voucher_user_bind_id where 1 = 1 ';

        if (input != null) {

            if (input.community_name != null && input.community_name != '') {
                sql += ' and b.community_name like "%' + input.community_name + '%"';
            }

            if (input.user_id != null && input.user_id != '') {
                sql += ' and b.user_id= "' + input.user_id + '"';
            }

            if (input.card_no != null && input.card_no != '') {
                sql += ' and b.card_no= "' + input.card_no + '"';
            }

            if (input.is_use != null) {
                sql += ' and b.is_use= "' + input.is_use + '"';
            }

            if (input.reward_source != null && input.reward_source != '') {
                sql += ' and b.reward_source= "' + input.reward_source + '"';
            }

            if (input.startTime != null && input.startTime != '') {
                sql += ' and b.create_time >' + input.startTime
            }

            if (input.endTime != null && input.endTime != '') {
                sql += ' and b.create_time <' + input.endTime
            }
        }
        return sql;
    };

    async.series([
        function (callbackInner) {

            var sql = getSql('b.*,t.voucher_val,p.deal_price,p.retailer_price') + ' limit ' + offset + ' ,' + listPageSize

            mysqlUtil.query(sql, function (err, result) {
                async.each(result, function (temp, callbackInner) {
                    users.findOne({_id: temp.user_id}, 'nickName', function (err, user) {
                        temp.nickName = user.nickName;
                        callbackInner();
                    })
                }, function (err) {
                    callbackInner(err, result);
                })
            });
        },
        function (callbackInner) {
            var sql = getSql('count(*) as count ');
            mysqlUtil.query(getSql('count(*) as count '), function (err, result) {
                var totalPage = Math.ceil(result[0].count / listPageSize);
                callbackInner(err, totalPage);
            });
        }
    ], callback);

};

exports.getRetailerSettlementService = function (input, callback) {

    var sql = 'SELECT r.id,r.code,r.retailer_name,SUM(v.voucher_val) as value,SUM(p.deal_price) as deal,SUM(p.retailer_price) as price' +
        ' FROM voucher_retailer_profile p,retailer r,voucher_type v' +
        ' WHERE p.retailer_id= r.id and p.voucher_type_id = v.id and p.`status`=0';

    if (input.retailer_name != null && input.retailer_name != '') {
        sql += ' and r.retailer_name like "%' + input.retailer_name + '%"';
    }

    if (input.code != null && input.code != '') {
        sql += ' and r.code =' + input.code;
    }

    if (input.startTime != null && input.startTime != '') {
        sql += ' and p.create_time >' + input.startTime
    }

    if (input.endTime != null && input.endTime != '') {
        sql += ' and p.create_time <' + input.endTime
    }

    sql += ' GROUP BY p.retailer_id';

    mysqlUtil.query(sql, callback);
};

exports.retailerBalanceService = function (input, callback) {
    var sql = 'UPDATE voucher_retailer_profile p SET p.status=1,p.close_time=' + input.closeTime + ' WHERE p.retailer_id =' + input.id;

    if (input.startTime != null && input.startTime != '') {
        sql += ' and p.create_time >' + input.startTime
    }

    if (input.endTime != null && input.endTime != '') {
        sql += ' and p.create_time <' + input.endTime
    }

    mysqlUtil.query(sql, callback);
};

exports.getVoucherStatistics = function (input, callback) {

    var currentPage = 1;
    if (input.currentPage != '' && input.currentPage != null) {
        currentPage = input.currentPage;
    }
    var pageSize = 10;
    var offset = (currentPage - 1) * pageSize;

    var getSql = function (type){
        var sql = "select "+type+" from voucher_user_bind as vub"
            +" left join voucher_retailer_profile as vrp on vub.id = vrp.voucher_user_bind_id"
            +" left join voucher_type as vt on vub.voucher_type_id = vt.id"
            +" where 1=1 ";

        if(input.communityName != null && input.communityName != ''){
            sql += " and vub.community_name like '%" + input.communityName + "%'";
        }
        if(input.voucherType != null && input.voucherType != ''){
            sql += " and vt.id = " + input.voucherType;
        }
        if(input.rewardSource != null && input.rewardSource != ''){
            sql += " and vub.reward_source = " + input.rewardSource;
        }
        if(input.isUse != null){
            sql += " and vub.is_use = " + input.isUse;
        }
        if(input.beginTime != null && input.beginTime != '' && input.endTime != null && input.endTime != ''){
            sql += " and vub.create_time between "+input.beginTime+" and "+input.endTime;
        }else if(input.beginTime != null && input.beginTime != ''){
            sql += " and vub.create_time > "+input.beginTime;
        }else if(input.endTime != null && input.endTime){
            sql += " and vub.create_time < "+input.endTime;
        }

        return sql;
    };

    async.series([
        function (callbackInner) {
            var sql = getSql("vub.community_name,vt.voucher_name ,count(*) voucher_count , sum(vt.voucher_val) as voucher_val");
            if(input.excludeUser == 1){
                users.find({$or:[{mobilePhone:/\b500/},{mobilePhone:/\b400/}]},"_id",function(err,result){
                    var ids = "";
                    for(var i = 0 ; i < result.length ; i ++){
                        if(i == 0){
                            ids += "'"+result[i]._id+"'";
                        }else{
                            ids += ",'"+result[i]._id+"'";
                        }
                    }
                    sql += " and vub.user_id not in ("+ids+")";
                    sql += " group by vub.community_name,vt.voucher_name limit "+offset+","+pageSize;
                    mysqlUtil.query(sql,callbackInner);
                });
            }else{
                sql += " group by vub.community_name,vt.voucher_name limit "+offset+","+pageSize;
                mysqlUtil.query(sql, callbackInner);
            }
        },
        function (callbackInner) {
            var sql = getSql("count(*) as count");
            if(input.excludeUser==1){
                users.find({$or:[{mobilePhone:/\b500/},{mobilePhone:/\b400/}]},"_id",function(err,result){
                    var ids = "";
                    for(var i = 0 ; i < result.length ; i ++){
                        if(i == 0){
                            ids += "'"+result[i]._id+"'";
                        }else{
                            ids += ",'"+result[i]._id+"'";
                        }
                    }
                    sql += " and vub.user_id not in ("+ids+")";
                    sql += " group by vub.community_name,vt.voucher_name ";
                    mysqlUtil.query(sql, function(err,result){
                        var totalPage = 0 ;
                        if(result[0] != null){
                            var count = result[0].count;
                            var totalPage = Math.ceil(count / pageSize);
                        }
                        callbackInner(err, totalPage);
                    });
                });
            }else{
                sql += " group by vub.community_name,vt.voucher_name ";

                mysqlUtil.query(sql, function(err,result){
                    var totalPage = 0 ;
                    if(result[0] != null){
                        var count = result[0].count;
                        var totalPage = Math.ceil(count / pageSize);
                    }

                    callbackInner(err, totalPage);
                });
            }
        }
    ], callback);

};

exports.getConsumeStatistics = function (input, callback) {

    var currentPage = 1;
    if (input.currentPage != '' && input.currentPage != null) {
        currentPage = input.currentPage;
    }
    var pageSize = 10;
    var offset = (currentPage - 1) * pageSize;

    var getSql = function(type){
        var sql = "select " + type
            +" from voucher_retailer_profile as vrp"
            +" inner join retailer as r on vrp.retailer_id = r.id"
            +" inner join voucher_type as vt on vrp.voucher_type_id = vt.id "
            +" where 1=1 ";


        if(input.retailerName != null && input.retailerName != ''){
            sql += " and r.retailer_name like '%" + input.retailerName + "%'";
        }
        if(input.voucherType != null && input.voucherType != ''){
            sql += " and vt.id = " + input.voucherType;
        }
        if(input.beginTime != null && input.beginTime != '' && input.endTime != null && input.endTime != ''){
            sql += " and vrp.create_time between "+input.beginTime+" and "+input.endTime;
        }else if(input.beginTime != null && input.beginTime != ''){
            sql += " and vrp.create_time >= "+input.beginTime;
        }else if(input.endTime != null && input.endTime){
            sql += " and vrp.create_time <= "+input.endTime;
        }

        sql += " group by retailer_id,voucher_type_id";
        return sql
    }



    async.series([
        function (callbackInner) {

            var sql = getSql("r.retailer_name,vt.voucher_name,count(*) as voucher_count,sum(vt.voucher_val) as voucher_val,sum(vrp.deal_price) as deal_price,sum(retailer_price) as retailer_price") + ' limit ' + offset + ' ,' + pageSize;

            mysqlUtil.query(sql,callbackInner);

        },
        function (callbackInner) {
            var sql = getSql('count(*) as count ');
            mysqlUtil.query(getSql('count(*) as count '), function (err, result) {
                var totalPage = 0 ;
                if(result[0] != null){
                    var count = result[0].count;
                    var totalPage = Math.ceil(count / pageSize);
                }
                callbackInner(err, totalPage);
            });
        }
    ], callback);

};

exports.getIsSettlementProfile = function (input, callback) {

    var currentPage = 1;
    if (input.currentPage != '' && input.currentPage != null) {
        currentPage = input.currentPage;
    }
    var pageSize = 10;
    var offset = (currentPage - 1) * pageSize;

    var getSql = function(type){
        var sql = "select " + type
            +" from voucher_retailer_profile as vrp"
            +" inner join retailer as r on vrp.retailer_id = r.id"
            +" where 1=1 ";


        if(input.retailerName != null && input.retailerName != ''){
            sql += " and r.retailer_name like '%" + input.retailerName + "%'";
        }
        if(input.beginTime != null && input.beginTime != '' && input.endTime != null && input.endTime != ''){
            sql += " and vrp.close_time between "+input.beginTime+" and "+input.endTime;
        }else if(input.beginTime != null && input.beginTime != ''){
            sql += " and vrp.close_time >= "+input.beginTime;
        }else if(input.endTime != null && input.endTime){
            sql += " and vrp.close_time <= "+input.endTime;
        }

        sql += " group by FROM_UNIXTIME(vrp.close_time, '%Y %D %M'), r.retailer_name ";
        return sql
    }



    async.series([
        function (callbackInner) {

            var sql = getSql("r.retailer_name,vrp.close_time,sum(vrp.deal_price+vrp.retailer_price) as total_price") + ' limit ' + offset + ' ,' + pageSize
            mysqlUtil.query(sql,callbackInner);

        },
        function (callbackInner) {
            var sql = getSql('count(*) as count ');
            mysqlUtil.query(getSql('count(*) as count '), function (err, result) {
                var totalPage = 0 ;
                if(result[0] != null){
                    var count = result[0].count;
                    var totalPage = Math.ceil(count / pageSize);
                }
                callbackInner(err, totalPage);
            });
        }
    ], callback);

};